package com.dldata.devtools30.utils;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhw on 2020-08-24.
 */
public class Pagination {

    public static final int NUMBERS_PER_PAGE = 20;
    private int pageSize = 0;
    private int limit = 20;
    private int totalSize;
    private List<Map<String, Object>> resultList;
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public Pagination() {

    }
    public Pagination(String sql, int pagesize, int limit, Map param,NamedParameterJdbcTemplate namedParameterJdbcTemplate){
        //pagesize=pagesize-1;
        setPageSize(pagesize);
        setLimit(limit);
        setjTemplate(namedParameterJdbcTemplate);
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");
        if (orderByIndex <= 0) {
//            throw new UnsupportedOperationException(
//                    "must specify ‘order by‘ statement to support limit operation with offset in sql server 2005");
            String tempSql=sql.toLowerCase().trim().replaceFirst("select","select top 0 ");
            StringBuilder orderBy=new StringBuilder();
            namedParameterJdbcTemplate.query(tempSql, param, new ResultSetExtractor<HashMap>() {
                @Override
                public HashMap extractData(ResultSet rs) throws SQLException, DataAccessException {
//                    rs.getMetaData().getColumnCount();
                    if(orderBy.length()==0){
                        orderBy.append(" order by ");
                        orderBy.append(rs.getMetaData().getColumnName(1));
                        orderBy.append(" ");
                    }
                    return new HashMap();
                }
            });
            sql=sql+orderBy.toString();
            orderByIndex = sql.toLowerCase().lastIndexOf("order by");
        }
        String totalSql = "select count(*) as totalsize from ("+sql.toLowerCase().substring(0, orderByIndex) +") totalTable ";

        setTotalSize(this.namedParameterJdbcTemplate.queryForObject(totalSql,param,Integer.class));

        String sqls = this.getLimitString(sql, pagesize, limit);
        setResultList(this.namedParameterJdbcTemplate.queryForList(sqls,param));
    }

    public String getLimitString(String sql, int offset, int limit) {
        return getLimitString(sql, offset > 0,offset,limit);
    }

    public String getLimitString(String sql, boolean hasOffset, int offset, int limit) {
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");
        if (orderByIndex <= 0) {
            throw new UnsupportedOperationException(
                    "must specify ‘order by‘ statement to support limit operation with offset in sql server 2005");
        }

        String sqlOrderBy = sql.substring(orderByIndex + 8);
        String sqlRemoveOrderBy = sql.substring(0, orderByIndex);
        int insertPoint = getSqlAfterSelectInsertPoint(sql);
        StringBuilder sbSql =  new StringBuilder(sql.length() + 100).append("with tempPagination as(").append(sqlRemoveOrderBy).insert(
                insertPoint + 23, " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy + ") as RowNumber,").append(
                ") select * from tempPagination ");
        if(offset != -1  && limit !=-1){
            sbSql.append("  where RowNumber between "+(((offset-1)*limit)+1)+" and "+(offset*limit)+"") ;
        }
        return sbSql.toString();
    }


    private static int getSqlAfterSelectInsertPoint(String sql) {
        int selectIndex = sql.toLowerCase().indexOf("select");
        final int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
        return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getLimit() {
        return limit;
    }
    public void setLimit(int limit) {
        this.limit = limit;
    }
    public int getTotalSize() {
        return totalSize;
    }
    public void setTotalSize(int totalSize) {
        this.totalSize = totalSize;
    }
    public List<Map<String, Object>> getResultList() {
        return resultList;
    }
    public void setResultList(List<Map<String, Object>> resultList) {
        this.resultList = resultList;
    }
    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setjTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }
}
